﻿using Blm.PgsqlDriver.Helpers;
using Blm.Utils.Extensions;
using ExcelReport;
using ExcelReport.Driver.NPOI;
using ExcelReport.Renderers;
using Language.Resource;
using Magicodes.ExporterAndImporter.Core;
using Magicodes.ExporterAndImporter.Core.Extension;
using Magicodes.ExporterAndImporter.Excel;
using Microsoft.Extensions.Localization;
using Microsoft.Extensions.Options;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using VisionCloud.Domain.Common;
using VisionCloud.Domain.DtoModel.Inner;
using VisionCloud.Domain.DtoModel.Ms;
using VisionCloud.Domain.DtoModel.Sys;
using VisionCloud.Domain.Repository.Interface.Ms;
using VisionCloud.Domain.Repository.Interface.Sys;
using VisionCloud.Utility;
using VisionCloud.Utility.Option;

namespace VisionCloud.Service.Ms
{
    /// <summary>
    /// 工效分析服务类
    /// </summary>
    public class EfficiencyAnalyzeService
    {
        public ISysUserRepository SysUserRepository { get; set; }

        public ICSMSTaskRepository CSMSTaskRepository { get; set; }

        public IStringLocalizer<ServiceResource> Localizer { get; set; }

        private UploadFileOptions uploadFileOptions;

        private OperatorInfo tokenModel => OperatorProvider.Get();

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="option"></param>
        public EfficiencyAnalyzeService(IOptionsMonitor<UploadFileOptions> option)
        {
            uploadFileOptions = option.CurrentValue;
        }

        /// <summary>
        /// 取所有关联用户工效信息
        /// </summary>
        private async Task<List<SysUserListDto>> GetRelationUsersWithCompany(string userName, Guid companyId)
        {
            List<NpgsqlParameter> parameters = new List<NpgsqlParameter>
            {
                new NpgsqlParameter("@YunUserGuid", tokenModel.YunUserGuid),
                new NpgsqlParameter("@CompanyId", companyId)
            };

            string likeSql = string.Empty;
            if (!string.IsNullOrEmpty(userName))
            {
                likeSql = @"AND ""UserName1"" LIKE @UserName1)";
                parameters.Add(new NpgsqlParameter("@UserName1", $"%{userName?.Replace("%", "\\%")}%"));
            }

            string sql = string.Format(
                VisionCloudGlobalContext.GetSection("sqls:sql:query_relation_users_with_company"),
                likeSql);

            if (companyId != Guid.Empty)
            {
                sql = string.Format(
                    VisionCloudGlobalContext.GetSection("sqls:sql:query_relation_users_with_company01"),
                    likeSql);
            }

            var userList = new List<SysUserListDto>();
            var resultList = await PgSqlHelper.QueryListAsync<(Guid UserId, string UserName)>(sql, parameters.ToArray());
            foreach (var item in resultList)
            {
                userList.Add(new SysUserListDto
                {
                    YunUserGuid = item.UserId,
                    Name = item.UserName,
                });
            }

            return userList;
        }

        /// <summary>
        /// 取所有关联用户工效信息
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="userName"></param>
        /// <returns></returns>
        private async Task<List<SysUserListDto>> GetRelationUsers(string userName = null, Guid? userId = null)
        {
            List<SysUserListDto> listOfUsers = new List<SysUserListDto>();

            Guid companyId = Guid.Empty;
            if (tokenModel.RoleNameList == "公司管理员")
            {
                companyId = tokenModel.CompanyGuid;
            }
            // 关联用户Id
            listOfUsers = await GetRelationUsersWithCompany(userName, companyId);

            var id = userId ?? tokenModel.Id;
            if (!listOfUsers.Exists(p => p.YunUserGuid == id))
            {
                var existedUser = await SysUserRepository.GetByIdAsync(id);
                if (existedUser != null)
                {
                    listOfUsers.Add(new SysUserListDto()
                    {
                        Name = existedUser.Name,
                        YunUserGuid = existedUser.YunUserGuid,
                    });  // 关联用户Id
                }
            }

            return listOfUsers;
        }

        /// <summary>
        /// 作者：黄钧航
        /// 获取所有用户的测量进度
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public async Task<List<MsSPersonnelDto>> GetUserProgress(MsUserDto user)
        {
            DateTime now = DateTime.Now;
            string condition = string.Empty;
            // 计算到昨日的工效
            List<NpgsqlParameter> parameters = new List<NpgsqlParameter>
            {
                new NpgsqlParameter("@Year",now.AddDays(-1).Year),

                new NpgsqlParameter("@Month",now.AddDays(-1).Month),

                new NpgsqlParameter("@Day",now.AddDays(-1).Day),

                new NpgsqlParameter("@Name",user.Name ?? string.Empty),

                new NpgsqlParameter("@UserGuid",user.Id.ToGuid())
            };

            List<SysUserListDto> listOfUsers = await GetRelationUsers(user.Name, user.Id.ToGuid());
            if (listOfUsers.Count > 0)
            {
                for (int i = 0; i < listOfUsers.Count; i++)
                {
                    condition += $" @UserGuid{i}, ";
                    parameters.Add(new NpgsqlParameter()
                    {
                        ParameterName = $"UserGuid{i}",
                        Value = listOfUsers[i].YunUserGuid,
                    });
                }
                condition = $"AND \"UserGuid\" in ({condition.Substring(0, condition.Length - 2)})";
            }

            List<MsSPersonnelDto> userList = new List<MsSPersonnelDto>();
            foreach (var item in listOfUsers)
            {
                var userDto = new MsSPersonnelDto()
                {
                    UserId = item.YunUserGuid.ToString(),
                    Name = item.Name,
                    TotalAreas = "0",
                    UserAccount = string.Empty,
                    TotalFloors = "0",
                };
                userList.Add(userDto);
            }

            string sql = string.Format(VisionCloudGlobalContext.GetSection("sqls:sql:query_user_progress"), condition);

            var progressList = await PgSqlHelper.QueryListAsync<UserProgressDto>(sql, parameters.ToArray());
            foreach (var item in progressList)
            {
                var existed = userList.FirstOrDefault(x => x.UserId == item.YunUserGuid.ToString());
                if (existed != null)
                {
                    existed.Name = item.Name;
                    // 若为整数则不显示小数
                    existed.TotalAreas = Math.Round(item.TotalAreas, 2).ToString().Replace(".00", "");
                    existed.UserAccount = item.Code;
                    existed.TotalFloors = item.TotalFloors.ToString();
                }
            }

            // 先按测量面积倒序排序，再按照名称正序排序
            userList.Sort((a, b) =>
            {
                if (a.UserId.ToGuid() == tokenModel.YunUserGuid)
                {
                    return -1;
                }
                else if (b.UserId.ToGuid() == tokenModel.YunUserGuid)
                {
                    return 1;
                }
                var c = (int)(decimal.Parse(b.TotalAreas) - decimal.Parse(a.TotalAreas));
                if (c == 0)
                {
                    c = string.Compare(a.Name, b.Name);
                }
                return c;
            });
            return userList;
        }

        /// <summary>
        /// 通用排序分页
        /// </summary>
        /// <param name="offset"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        private string GetProgeressListQuerySql(int offset, int pageSize) => $@"ORDER BY ""TotalAreas"" DESC,""Name"" DESC
                                   OFFSET {offset} LIMIT {pageSize} ";

        /// <summary>
        /// 根据枚举转化对应的查询条件
        /// </summary>
        /// <param name="property"></param>
        /// <param name="tableAlias"></param>
        /// <returns></returns>
        private string TransferTaskPropertyWhereSql(EnumTaskProperty property, string tableAlias)
        {
            switch (property)
            {
                case EnumTaskProperty.结构初测:
                    return $@"({tableAlias}.""ProjectStandardName"" = '结构工程' and  {tableAlias}.""TaskProperty"" = '初测')";

                case EnumTaskProperty.结构复测:
                    return $@"({tableAlias}.""ProjectStandardName"" = '结构工程' and  {tableAlias}.""TaskProperty"" = '复测')";

                case EnumTaskProperty.结构其他:
                    return $@"({tableAlias}.""ProjectStandardName"" = '结构工程' and  {tableAlias}.""TaskProperty"" != '初测' and {tableAlias}.""TaskProperty"" != '复测')";

                case EnumTaskProperty.砌筑初测:
                    return $@"({tableAlias}.""ProjectStandardName"" = '砌筑工程' and  {tableAlias}.""TaskProperty"" = '初测')";

                case EnumTaskProperty.砌筑复测:
                    return $@"({tableAlias}.""ProjectStandardName"" = '砌筑工程' and  {tableAlias}.""TaskProperty"" = '复测')";

                case EnumTaskProperty.砌筑其他:
                    return $@"({tableAlias}.""ProjectStandardName"" = '砌筑工程' and  {tableAlias}.""TaskProperty"" != '初测' and {tableAlias}.""TaskProperty"" != '复测')";

                case EnumTaskProperty.抹灰初测:
                    return $@"({tableAlias}.""ProjectStandardName"" = '抹灰工程' and  {tableAlias}.""TaskProperty"" = '初测')";

                case EnumTaskProperty.抹灰复测:
                    return $@"({tableAlias}.""ProjectStandardName"" = '抹灰工程' and  {tableAlias}.""TaskProperty"" = '复测')";

                case EnumTaskProperty.抹灰其他:
                    return $@"({tableAlias}.""ProjectStandardName"" = '抹灰工程' and  {tableAlias}.""TaskProperty"" != '初测' and {tableAlias}.""TaskProperty"" != '复测')";

                default:
                    return "";
            }
        }

        /// <summary>
        /// 获取TaskProperty的查询条件
        /// </summary>
        /// <param name="list"></param>
        /// <param name="tableAlias"></param>
        /// <returns></returns>
        private string GetTaskPropertyWhereSql(List<EnumTaskProperty> list, string tableAlias)
        {
            if (list == null || list.Count == 0)
                return string.Empty;
            List<string> result = new List<string>();
            foreach (var item in list)
            {
                result.Add(TransferTaskPropertyWhereSql(item, tableAlias));
            }
            return $" and ({string.Join(" or ", result)}) ";
        }

        /// <summary>
        /// 获取TaskTypeName的查询条件
        /// </summary>
        /// <param name="taskTypeArray"></param>
        /// <param name="tableAlis"></param>
        /// <returns></returns>
        private string GetTaskTypeNameWhereSql(List<EnumTaskTypeName> taskTypeArray, string tableAlis)
        {
            var taskTypeWhereSql = string.Empty;
            if (taskTypeArray != null && taskTypeArray.Count > 0)
            {
                taskTypeWhereSql = $@" and {tableAlis}.""TaskTypeName"" in ({string.Join(',', taskTypeArray.Select(p => $"'{p}'"))}) ";
            }
            return taskTypeWhereSql;
        }

        /// <summary>
        /// 获取模糊查询条件
        /// </summary>
        /// <param name="query"></param>
        /// <param name="tableAlis"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        private (string, List<NpgsqlParameter>) GetNameLikeWhereSql(string query, string tableAlis, string field)
        {
            List<NpgsqlParameter> parameters = new List<NpgsqlParameter>();
            var queryWhereSql = string.Empty;
            if (!string.IsNullOrEmpty(query))
            {
                //PDBHelper有Bug，使用参数化有时候查询不出来数据
                queryWhereSql = $@" and {tableAlis}.""{field}"" like '%{query.ReplaceSQLChar()}%' ";
            }

            return (queryWhereSql, parameters);
        }

        /// <summary>
        /// 获取用户关联用户查询条件
        /// </summary>
        /// <param name="tableAlis"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        public async ValueTask<string> GetRelationUserSql(string tableAlis, string field)
        {
            var sql = string.Empty;
            var listOfUsers = await GetRelationUsers();
            if (listOfUsers != null && listOfUsers.Count > 0)
            {
                sql = $@" and {tableAlis}.""{field}"" in ({string.Join(',', listOfUsers.Select(p => $"'{p.YunUserGuid}'"))})";
            }

            return sql;
        }

        /// <summary>
        /// 分页获取用户的测量进度列表
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<ApiPageResult> GetUserProgressPage(AreaFloorRequestDto condition)
        {
            var start_date = condition.StartTime ?? DateTime.MinValue;
            var end_date = condition.EndTime ?? DateTime.Now;
            var offset = Math.Max(condition.CurrentPage - 1, 0) * condition.PageSize;

            #region 数据查询

            string sql = VisionCloudGlobalContext.GetSection("sqls:sql:query_user_progress_page");
            List<NpgsqlParameter> paraList = GetDatetimeInitParameters(start_date, end_date);

            //关联用户查询条件
            var userWhereSql = await GetRelationUserSql("T1", "UserGuid");
            var joinUserSql = userWhereSql.Replace("T1", "S1").Replace("UserGuid", "YunUserGuid");

            //关联项目限定条件
            var projectLimitation = tokenModel.IsCompanyAdmin ? "" : $@"AND EXISTS (SELECT 1 FROM ""MsUserToProject"" AS up WHERE up.""YunProjectGuid"" = t1.""ProjectGuid"" AND up.""YunUserGuid"" = '{tokenModel.Id}')";

            //模糊查询条件
            var (queryWhereSql, parameters) = GetNameLikeWhereSql(condition.Query, "S1", "Name");
            if (parameters.Count > 0)
                paraList.AddRange(parameters);

            //总数量查询Sql
            var totalSelectSql = "count(1)";

            //列表查询Sql
            var listSelectSql = @"S1.""Name"",S1.""YunUserGuid"" ""Id"",COALESCE(S2.""TotalFloors"",0) ""TotalFloors"",
                        COALESCE(S2.""TotalAreas"", 0) ""TotalAreas""";

            //查询总数量
            var totalSql = string.Format(sql, totalSelectSql, projectLimitation, queryWhereSql, joinUserSql, string.Empty);
            var total = await PgSqlHelper.QueryScalarAsync<long>(CommandType.Text, totalSql, false, paraList.ToArray());

            //查询当前分页数据列表
            var listSql = string.Format(sql, listSelectSql, projectLimitation, queryWhereSql, joinUserSql, GetProgeressListQuerySql(offset, condition.PageSize));
            var list = await PgSqlHelper.QueryListAsync<AreaFloorResponseDto>(CommandType.Text, listSql, false, paraList.ToArray());

            #endregion 数据查询

            return new ApiPageResult
            {
                CurrentPage = condition.CurrentPage,
                PageSize = condition.PageSize,
                Query = condition.Query,
                PageData = list,
                Total = total
            };
        }

        /// <summary>
        /// 获取某个用户的测量进度详情
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<EfficiencyDetailResponseDto> GetUserEfficiencyDetail(EfficiencyDetailRequestDto condition)
        {
            var start_date = condition.StartTime ?? DateTime.MinValue;
            var end_date = condition.EndTime ?? DateTime.Now;
            var responseDto = new EfficiencyDetailResponseDto
            {
                Id = condition.Id,
                Name = condition.Name,
                StartTime = start_date.ToDateStandardString(),
                EndTime = end_date.ToDateStandardString()
            };

            #region 工效整体数据

            //关联项目限定条件
            var projectLimitation = tokenModel.IsCompanyAdmin ? "" : $@" AND EXISTS (SELECT 1 FROM ""MsUserToProject"" AS up WHERE up.""YunProjectGuid"" = t1.""ProjectGuid"" AND up.""YunUserGuid"" = '{tokenModel.Id}')";

            // 取当前用户整体工效信息
            string sql = string.Format(
                VisionCloudGlobalContext.GetSection("sqls:sql:query_user_efficiency_detail01"),
                condition.Id, projectLimitation);

            var paraList = GetDatetimeInitParameters(start_date, end_date);
            GetEfficiencySummaryInfos(sql, responseDto, paraList.ToArray());

            #endregion 工效整体数据

            #region 工效日度数据

            string sql2 = string.Format(
                VisionCloudGlobalContext.GetSection("sqls:sql:query_user_efficiency_detail02"),
                condition.Id, projectLimitation);
            responseDto.DailyInfos = await PgSqlHelper.QueryListAsync<DetailInfo>(CommandType.Text, sql2);

            #endregion 工效日度数据

            #region 工效月度数据

            string sql3 = string.Format(
                VisionCloudGlobalContext.GetSection("sqls:sql:query_user_efficiency_detail03"),
                condition.Id, projectLimitation);
            responseDto.MonthlyInfos = await PgSqlHelper.QueryListAsync<DetailInfo>(CommandType.Text, sql3);

            #endregion 工效月度数据

            return responseDto;
        }

        /// <summary>
        /// 作者：黄钧航
        /// 获取当前用户的测量进度
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public async Task<MsSPersonnelEfficiencyDto> GetMsSPersonnelEfficiencyData(MsUserDto user)
        {
            #region 工效整体数据

            DateTime now = DateTime.Now;
            // 获取当前用户昨日工效
            var userList = await GetUserProgress(user);
            if (userList == null || userList.Count == 0)
            {
                return null;
            }

            List<NpgsqlParameter> parameters = new List<NpgsqlParameter>
            {
                new NpgsqlParameter("@Year",now.Year),

                new NpgsqlParameter("@Month",now.Month),

                new NpgsqlParameter("@Day",now.Day),

                new NpgsqlParameter("@UserGuid",user.Id.ToGuid())
            };

            // 取当前用户整体工效信息
            string sql = string.Format(
                       VisionCloudGlobalContext.GetSection("sqls:sql:query_msspersonnel_efficiency_data01"));
            var msSPersonnelEfficiencyDto = new MsSPersonnelEfficiencyDto
            {
                UserId = userList[0].UserId,
                UserName = userList[0].Name,
                SummaryInfos = new SummaryInfo()
                {
                    // 昨日总测量面积
                    YesterdayAreas = userList[0].TotalAreas,
                    // 昨日总测量层
                    YesterdayFloors = userList[0].TotalFloors,
                }
            };

            var (progressDto, workdayDto) = await PgSqlHelper.QueryPipeResultAsync<UserProgressDto, WorkDayDto>(sql, false, parameters.ToArray());
            if (progressDto != null)
            {
                // 总测量楼层
                msSPersonnelEfficiencyDto.SummaryInfos.TotalFloors = progressDto.TotalFloors.ToString();
                // 总测量面积（若为整数则不显示小数）
                msSPersonnelEfficiencyDto.SummaryInfos.TotalAreas = Math.Round(progressDto.TotalAreas, 2).ToString().Replace(".00", "");
            }
            if (workdayDto != null)
            {
                // 总工作时间（天）
                msSPersonnelEfficiencyDto.SummaryInfos.WorkDays = workdayDto.WorkDays.ToString();
            }

            #endregion 工效整体数据

            #region 工效日度数据

            string sql2 = string.Format(VisionCloudGlobalContext.GetSection("sqls:sql:query_msspersonnel_efficiency_data02"));

            var dayEfficiencyList = await PgSqlHelper.QueryListAsync<UserProgressDto>(sql2, parameters.ToArray());
            var detailInfos = new List<DetailInfo>();
            foreach (var item in dayEfficiencyList)
            {
                var data = new DetailInfo()
                {
                    Year = item.Year.ToString(),
                    Month = item.Month.ToString(),
                    Title = item.Day.ToString(),
                    Areas = item.TotalAreas.ToString(),
                    Floors = item.TotalFloors.ToString(),
                };
                detailInfos.Add(data);
            }
            msSPersonnelEfficiencyDto.DailyInfos = detailInfos;

            #endregion 工效日度数据

            #region 工效月度数据

            string sql3 = string.Format(VisionCloudGlobalContext.GetSection("sqls:sql:query_msspersonnel_efficiency_data03"));

            var monthEfficiencyList = await PgSqlHelper.QueryListAsync<UserProgressDto>(sql3, parameters.ToArray());
            detailInfos.Clear();
            foreach (var item in monthEfficiencyList)
            {
                var data = new DetailInfo()
                {
                    Year = item.Year.ToString(),
                    Month = item.Month.ToString(),
                    Title = item.Day.ToString(),
                    Areas = item.TotalAreas.ToString(),
                    Floors = item.TotalFloors.ToString(),
                };
                detailInfos.Add(data);
            }
            msSPersonnelEfficiencyDto.MonthlyInfos = detailInfos;

            #endregion 工效月度数据

            return msSPersonnelEfficiencyDto;
        }

        /// <summary>
        /// 分页获取项目的测量进度列表
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<ApiPageResult> GetProjectProgressPage(AreaFloorRequestDto condition)
        {
            var result = new ApiPageResult { CurrentPage = condition.CurrentPage, PageSize = condition.PageSize };

            var sqlParameters = new List<NpgsqlParameter>
            {
                new NpgsqlParameter("startDate",condition.StartTime ?? DateTime.MinValue),
                new NpgsqlParameter("userId",tokenModel.Id),
                new NpgsqlParameter("queryProject",condition.Query??string.Empty),
                new NpgsqlParameter("endDate",condition.EndTime ?? DateTime.Now),
                new NpgsqlParameter("offSet",Math.Max(condition.CurrentPage - 1, 0) * condition.PageSize),
                new NpgsqlParameter("companyId",tokenModel.CompanyGuid),
                new NpgsqlParameter("pageSize",condition.PageSize)
            };

            #region 数据查询

            string sql = VisionCloudGlobalContext.GetSection("sqls:sql:query_project_progress_page");
            var ignoreStr = "--";
            var userConditionIgnore = tokenModel.IsCompanyAdmin ? ignoreStr : string.Empty;
            string queryIgnore = string.IsNullOrEmpty(condition.Query) ? ignoreStr : string.Empty;

            //查询总数量
            var totalSql = string.Format(sql, "count(0)", userConditionIgnore, ignoreStr, queryIgnore);
            var total = await PgSqlHelper.QueryScalarAsync<long>(totalSql, sqlParameters.ToArray());
            if (total > 0)
            {
                result.Total = total;

                //查询当前分页数据列表
                var listSql = string.Format(sql, "*", userConditionIgnore, string.Empty, queryIgnore);
                result.PageData = await PgSqlHelper.QueryListAsync<AreaFloorResponseDto>(listSql, sqlParameters.ToArray());
            }

            #endregion 数据查询

            return result;
        }

        /// <summary>
        /// 获取开始时间和结束时间的参数化
        /// </summary>
        /// <param name="start_date"></param>
        /// <param name="end_date"></param>
        /// <returns></returns>
        private static List<NpgsqlParameter> GetDatetimeInitParameters(DateTime start_date, DateTime end_date)
        {
            return new List<NpgsqlParameter>
            {
                new NpgsqlParameter()
                {
                    ParameterName = "SYear",
                    Value = start_date.Year,
                },

                new NpgsqlParameter()
                {
                    ParameterName = "SMonth",
                    Value = start_date.Month,
                },

                new NpgsqlParameter()
                {
                    ParameterName = "SDay",
                    Value = start_date.Day,
                },

                new NpgsqlParameter()
                {
                    ParameterName = "EYear",
                    Value = end_date.Year,
                },

                new NpgsqlParameter()
                {
                    ParameterName = "EMonth",
                    Value = end_date.Month,
                },

                new NpgsqlParameter()
                {
                    ParameterName = "EDay",
                    Value = end_date.Day,
                }
            };
        }

        /// <summary>
        /// 获取某个项目的测量进度详情
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<EfficiencyDetailResponseDto> GetProjectEfficiencyDetail(EfficiencyDetailRequestDto condition)
        {
            var start_date = condition.StartTime ?? DateTime.MinValue;
            var end_date = condition.EndTime ?? DateTime.Now;
            var responseDto = new EfficiencyDetailResponseDto
            {
                Id = condition.Id,
                Name = condition.Name,
                StartTime = start_date.ToDateStandardString(),
                EndTime = end_date.ToDateStandardString()
            };

            #region 工效整体数据

            var taskTypeSql = GetTaskTypeNameWhereSql(condition.TaskTypeArray, "t1");
            var taskPropertySql = GetTaskPropertyWhereSql(condition.TaskPropertyArray, "t1");

            // 取当前用户整体工效信息

            string sql = string.Format(
                VisionCloudGlobalContext.GetSection("sqls:sql:query_project_efficiency_detail01"),
                condition.Id, taskTypeSql);
            var paraList = GetDatetimeInitParameters(start_date, end_date);
            GetEfficiencySummaryInfos(sql, responseDto, paraList.ToArray());

            #endregion 工效整体数据

            #region 工效日度数据

            string sql2 = string.Format(VisionCloudGlobalContext.GetSection("sqls:sql:query_project_efficiency_detail02"), condition.Id);
            responseDto.DailyInfos = await PgSqlHelper.QueryListAsync<DetailInfo>(CommandType.Text, sql2);

            #endregion 工效日度数据

            #region 工效月度数据

            string sql3 = string.Format(VisionCloudGlobalContext.GetSection("sqls:sql:query_project_efficiency_detail03"), condition.Id);
            responseDto.MonthlyInfos = await PgSqlHelper.QueryListAsync<DetailInfo>(CommandType.Text, sql3);

            #endregion 工效月度数据

            return responseDto;
        }

        /// <summary>
        /// 分页获取设备测量进度列表
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<ApiPageResult> GetDeviceProgressPage(AreaFloorRequestDto condition)
        {
            var paraList = new List<NpgsqlParameter>
            {
                new NpgsqlParameter("startDate",condition.StartTime ?? DateTime.MinValue),
                new NpgsqlParameter("endDate",condition.EndTime ?? DateTime.Now),
                new NpgsqlParameter("userId",tokenModel.Id),
                new NpgsqlParameter("queryDevice",condition.Query??string.Empty),
                new NpgsqlParameter("companyId",tokenModel.CompanyGuid.ToString()),
                new NpgsqlParameter("offSet",Math.Max(condition.CurrentPage - 1, 0) * condition.PageSize),
                new NpgsqlParameter("pageSize",condition.PageSize)
            };

            #region 数据查询

            string sql = VisionCloudGlobalContext.GetSection("sqls:sql:query_device_progress_page");
            var ignoreStr = "--";
            var userLimitIgnore = tokenModel.IsCompanyAdmin ? ignoreStr : string.Empty;
            var queryDeviceIgnore = string.IsNullOrEmpty(condition.Query) ? ignoreStr : string.Empty;
            var totalSql = string.Format(sql, "count(0)", userLimitIgnore, ignoreStr, queryDeviceIgnore);
            var total = await PgSqlHelper.QueryScalarAsync<long>(totalSql, paraList.ToArray());

            List<AreaFloorResponseDto> list = null;
            if (total > 0)
            {
                //查询当前分页数据列表
                var listSql = string.Format(sql, "*", userLimitIgnore, string.Empty, queryDeviceIgnore);
                list = await PgSqlHelper.QueryListAsync<AreaFloorResponseDto>(listSql, paraList.ToArray());
            }


            #endregion 数据查询

            return new ApiPageResult
            {
                CurrentPage = condition.CurrentPage,
                PageSize = condition.PageSize,
                Query = condition.Query,
                PageData = list,
                Total = (long)total
            };
        }

        /// <summary>
        /// 获取某个设备的测量进度详情
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public async Task<EfficiencyDetailResponseDto> GetDeviceEfficiencyDetail(EfficiencyDetailRequestDto condition)
        {
            var start_date = condition.StartTime ?? DateTime.MinValue;
            var end_date = condition.EndTime ?? DateTime.Now;
            var responseDto = new EfficiencyDetailResponseDto
            {
                Id = condition.Id,
                Name = condition.Name,
                StartTime = start_date.ToDateStandardString(),
                EndTime = end_date.ToDateStandardString()
            };

            var parameters = new List<NpgsqlParameter>
            {
                new NpgsqlParameter("startDate",condition.StartTime ?? DateTime.MinValue),
                new NpgsqlParameter("endDate", condition.EndTime ?? DateTime.Now),
                new NpgsqlParameter("userId",tokenModel.Id),
                new NpgsqlParameter("deviceId",condition.Id),
                new NpgsqlParameter("companyId",tokenModel.CompanyGuid)
            };

            string sql = VisionCloudGlobalContext.GetSection("sqls:sql:query_device_efficiency_detail");
            var userLimitIgnor = tokenModel.IsCompanyAdmin ? "--" : string.Empty;
            var querySql = string.Format(sql, userLimitIgnor);
            var efficiencyDedtails = await PgSqlHelper.QueryListAsync<EfficiencyDedtail>(querySql, parameters.ToArray());
            if (efficiencyDedtails.Count > 0)
            {
                //responseDto.SummaryInfos
                var TotalAreas = efficiencyDedtails.Sum(x => x.Areas);
                var TotalFloors = efficiencyDedtails.Sum(x => x.Floors);
                var WorkDays = efficiencyDedtails.Count();
                var AvgAreas = TotalAreas / WorkDays;
                var AvgFloors = TotalFloors / WorkDays;
                responseDto.SummaryInfos = new EfficiencyStatInfoDto
                {
                    TotalAreas = TotalAreas.ToString("0.00"),
                    AvgFloors = AvgFloors.ToString(),
                    AvgAreas = AvgAreas.ToString(),
                    TotalFloors = TotalFloors.ToString(),
                    WorkDays = WorkDays.ToString(),
                };

                //responseDto.DailyInfos
                responseDto.DailyInfos = efficiencyDedtails.Select(x => new DetailInfo
                {
                    Areas = x.Areas.ToString("0.00"),
                    Floors = x.Floors.ToString(),
                    Year = x.WorkDay.Year.ToString(),
                    Month = x.WorkDay.Month.ToString(),
                    Title = x.WorkDay.Day.ToString(),
                }).ToList();

                //responseDto.MonthlyInfos
                responseDto.MonthlyInfos = efficiencyDedtails
                                .GroupBy(d => new { d.WorkDay.Year, d.WorkDay.Month })
                                .Select(g => new DetailInfo
                                {
                                    Year = g.Key.Year.ToString(),
                                    Month = g.Key.Month.ToString(),
                                    Areas = g.Sum(d => d.Areas).ToString("0.00"),
                                    Floors = g.Sum(d => d.Floors).ToString(),
                                }).ToList();
            }

            return responseDto;
        }

        private class EfficiencyDedtail
        {
            public double Areas { get; set; }
            public int Floors { get; set; }
            public DateTime WorkDay { get; set; }
        }

        /// <summary>
        /// 获取工效的统计和
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="responseDto"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private async Task GetEfficiencySummaryInfos(string sql, EfficiencyDetailResponseDto responseDto, NpgsqlParameter[] parameters)
        {
            var progressDto = await PgSqlHelper.QueryOneAsync<UserProgressDto>(sql, parameters);

            var areas = progressDto?.TotalAreas.ToString();// 总测量面积
            var days = progressDto?.WorkDays?.ToString();// 总工作时间（天）
            var daysInt = days?.ToInt();
            if (areas?.Length > 0 && daysInt.Value > 0)
            {
                var floors = progressDto?.TotalFloors.ToString(); // 总测量楼层
                responseDto.SummaryInfos = new EfficiencyStatInfoDto
                {
                    TotalAreas = areas,
                    TotalFloors = floors,
                    WorkDays = days,
                    AvgAreas = (areas.ToDecimal() / daysInt).ToString(),
                    AvgFloors = (floors.ToInt() / daysInt).ToString(),
                };
            }
        }

        /// <summary>
        /// 作者：黄钧航
        /// 工效分析下载数据
        /// </summary>
        /// <param name="user">用户信息</param>
        /// <returns></returns>
        public async Task<string> EfficiencyDataToExcelFile(MsUserDto user)
        {
            var msSPersonnelEfficiencyDto = await GetMsSPersonnelEfficiencyData(user);
            if (msSPersonnelEfficiencyDto != null)
            {
                return BuildlEfficiencyExcel(msSPersonnelEfficiencyDto, user);
            }
            throw new OperationException(Localizer["YB0182"].Value);
        }

        /// <summary>
        /// 作者：黄钧航
        /// 创建工效分析
        /// </summary>
        /// <returns></returns>
        private string BuildlEfficiencyExcel(MsSPersonnelEfficiencyDto msSPersonnelEfficiencyDto, MsUserDto user)
        {
            #region 准备模板路径和excel导出路径

            var templatePath = Path.Combine(uploadFileOptions.ParentDir, "SystemFiles/ExcelTemplates", "工效统计表模板-v3.xlsx");

            #endregion 准备模板路径和excel导出路径

            #region 整体工效信息

            var summaryInfos = msSPersonnelEfficiencyDto.SummaryInfos;

            var summaryExcelData = new SummaryEfficiencyStatis()
            {
                SeqNo = "1",
                YesterdayFloors = summaryInfos.YesterdayFloors,
                YesterdayAreas = summaryInfos.YesterdayAreas,
                TotalFloors = summaryInfos.TotalFloors,
                TotalAreas = summaryInfos.TotalAreas,
                WorkingDays = summaryInfos.WorkDays,
            };

            var userId = user.Id.ToGuid();
            var userInfo = SysUserRepository.FirstOrDefault(p => p.YunUserGuid == userId);
            summaryExcelData.UserName = userInfo?.Name;
            summaryExcelData.UserAccount = userInfo?.Code;

            var excelPath = Path.Combine(uploadFileOptions.ParentDir, uploadFileOptions.TemporaryFiles);
            if (!Directory.Exists(excelPath)) Directory.CreateDirectory(excelPath);
            excelPath = Path.Combine(excelPath, msSPersonnelEfficiencyDto.UserName + $"工效统计表_{StatisService.GetRandomNumber()}.xlsx");

            List<SummaryEfficiencyStatis> summaryExcelDatas = new List<SummaryEfficiencyStatis>
            {
                summaryExcelData
            };

            #endregion 整体工效信息

            #region 日工效汇总

            List<DailyEfficiencyStatis> dailyEfficiencyStatises = new List<DailyEfficiencyStatis>();
            foreach (var item in msSPersonnelEfficiencyDto.DailyInfos)
            {
                var dailyEfficiencyStatis = new DailyEfficiencyStatis()
                {
                    DateStr = $"{item.Year}/{item.Month}/{item.Title}",
                    UserName = summaryExcelDatas[0].UserName,
                    TotalFloors = item.Floors,
                    TotalAreas = item.Areas,
                    UserAccount = summaryExcelDatas[0].UserAccount,
                };
                dailyEfficiencyStatises.Add(dailyEfficiencyStatis);
            }

            #endregion 日工效汇总

            #region 月工效汇总

            List<MonthlyEfficiencyStatis> monthlyEfficiencyStatises = new List<MonthlyEfficiencyStatis>();
            foreach (var item in msSPersonnelEfficiencyDto.MonthlyInfos)
            {
                var monthlyEfficiencyStatis = new MonthlyEfficiencyStatis()
                {
                    DateStr = $"{item.Year}/{item.Month}",
                    UserName = summaryExcelDatas[0].UserName,
                    TotalFloors = item.Floors,
                    TotalAreas = item.Areas,
                    UserAccount = summaryExcelDatas[0].UserAccount,
                };
                monthlyEfficiencyStatises.Add(monthlyEfficiencyStatis);
            }

            #endregion 月工效汇总

            #region 生成EXCEL

            Configurator.Put(".xlsx", new WorkbookLoader());
            try
            {
                ExportHelper.ExportToLocal(templatePath, excelPath,
                        new SheetRenderer("整体数据",
                        new ParameterRenderer("StatisDate", DateTime.Now.ToString("yyyy-MM-dd")),
                            new RepeaterRenderer<SummaryEfficiencyStatis>("Roster", summaryExcelDatas,
                                new ParameterRenderer<SummaryEfficiencyStatis>("SeqNo", t => t.SeqNo),
                                new ParameterRenderer<SummaryEfficiencyStatis>("UserName", t => t.UserName),
                                new ParameterRenderer<SummaryEfficiencyStatis>("YesterdayFloors", t => t.YesterdayFloors),
                                new ParameterRenderer<SummaryEfficiencyStatis>("YesterdayAreas", t => t.YesterdayAreas),
                                new ParameterRenderer<SummaryEfficiencyStatis>("TotalFloors", t => t.TotalFloors),
                                new ParameterRenderer<SummaryEfficiencyStatis>("TotalAreas", t => t.TotalAreas),
                                new ParameterRenderer<SummaryEfficiencyStatis>("WorkingDays", t => t.WorkingDays),
                                new ParameterRenderer<SummaryEfficiencyStatis>("UserAccount", t => t.UserAccount)
                                )
                            )
                        ,

                        new SheetRenderer("日统计",
                            new RepeaterRenderer<DailyEfficiencyStatis>("Roster", dailyEfficiencyStatises,
                                new ParameterRenderer<DailyEfficiencyStatis>("DateStr", t => t.DateStr),
                                new ParameterRenderer<DailyEfficiencyStatis>("UserName", t => t.UserName),
                                new ParameterRenderer<DailyEfficiencyStatis>("TotalFloors", t => t.TotalFloors),
                                new ParameterRenderer<DailyEfficiencyStatis>("TotalAreas", t => t.TotalAreas),
                                new ParameterRenderer<DailyEfficiencyStatis>("UserAccount", t => t.UserAccount)
                                )
                            )
                        ,
                        new SheetRenderer("月统计",
                            new RepeaterRenderer<MonthlyEfficiencyStatis>("Roster", monthlyEfficiencyStatises,
                                new ParameterRenderer<MonthlyEfficiencyStatis>("DateStr", t => t.DateStr),
                                new ParameterRenderer<MonthlyEfficiencyStatis>("UserName", t => t.UserName),
                                new ParameterRenderer<MonthlyEfficiencyStatis>("TotalFloors", t => t.TotalFloors),
                                new ParameterRenderer<MonthlyEfficiencyStatis>("TotalAreas", t => t.TotalAreas),
                                new ParameterRenderer<MonthlyEfficiencyStatis>("UserAccount", t => t.UserAccount)
                                )
                            )
                        );
            }
            catch (Exception e)
            {
                Logger.Error("生成进度Excel异常", e);
            }

            #endregion 生成EXCEL

            return excelPath.Substring(uploadFileOptions.ParentDir.Length);
        }

        /// <summary>
        /// 作者：黄钧航
        /// 获取总工效数据
        /// [公司名称]总体工效情况 [开始时间]- [结束时间])
        /// 机器总数 (台)	测量员数量 (人)	测量总面积 (万m)	有效总时间 (台日)	总时间 (台日)	平均工效 (m/台日)	时效
        /// 20	20	240.14	1621	1968	1481	82.4%
        ///
        /// 例如检索时间是[1.30],1台机器,11号进场,11~25号进行过测量,测量面积是3万m
        /// 测量员总数:同上		机器总数:检索结束时间的机器总数量。如果做不到,就用当前的机器总数。
        ///
        /// 测量总面积:所有机器在检索时间段内测量的总面积。=3万m
        /// 有效总时间所有机器在检索时间段内实际工作过的时间。三15台日
        /// 总时间:在检索时间段内的总台日,扣除了机器没进场的时间。三20 台日
        /// 平均工效:测量总面积/有效总时间。三2000m/台日
        /// 时效:有效总时间/总时间。=75%
        /// </summary>
        /// <returns></returns>
        public async Task<SummayWorkEfficiency> GetEfficiencySummaryData(TimeConditon condition)
        {
            #region 总工效信息

            //任务类型查询条件
            var taskTypeWhereSql = GetTaskTypeNameWhereSql(condition.TaskTypeArray, "T1");

            //工程阶段查询条件
            var taskPropertyWhereSql = GetTaskPropertyWhereSql(condition.TaskPropertyArray, "T1");

            DateTime startTime = DateTime.Parse(condition.StartTime);
            DateTime endTime = DateTime.Parse(condition.EndTime);
            List<NpgsqlParameter> parameters = new List<NpgsqlParameter>
            {
                new NpgsqlParameter("startDate", startTime),

                new NpgsqlParameter("endDate", endTime),

                new NpgsqlParameter("SYear",startTime.Year),

                new NpgsqlParameter("SMonth",startTime.Month),

                new NpgsqlParameter("SDay",startTime.Day),

                new NpgsqlParameter("EYear",endTime.Year),

                new NpgsqlParameter("EMonth",endTime.Month),

                new NpgsqlParameter("EDay",endTime.Day),

                new NpgsqlParameter("CompanyId",tokenModel.CompanyGuid)
            };
            var totalDays = (int)DateTime.Parse(condition.EndTime).Subtract(DateTime.Parse(condition.StartTime)).TotalDays + 1;  // 总用时(天)

            SummayWorkEfficiency summayWorkEfficiency = new SummayWorkEfficiency();
            // 总工效数据
            string sql = string.Format(
                VisionCloudGlobalContext.GetSection("sqls:sql:query_efficiency_summary_data"), taskTypeWhereSql);

            using (DataSet ds = PgSqlHelper.ExecuteDataSet(sql, parameters.ToArray()))
            {
                if (ds != null && ds.Tables.Count != 0)
                {
                    // 获取实际有数据的日期
                    DateTime? startWorkingDay = null;
                    if (ds.Tables[1].Rows.Count > 0)
                    {
                        DateTime result; 
                        DateTime.TryParse($"{ds.Tables[1].Rows[0][2]}", out result); // 年-月-日
                        startWorkingDay = result;
                    }

                    // 最近有数据上传时间作为起始时间
                    if (condition.StartTime == DateTime.MinValue.ToString("yyyy-MM-dd"))
                    {
                        condition.StartTime = startWorkingDay?.ToString("yyyy-MM-dd");
                    }

                    // 计算总时间
                    if (ds.Tables[2].Rows.Count > 0)
                    {
                        var deviceEfficiency = ds.Tables[2].ToList<DeviceEfficiencyDto>();
                        totalDays = 0;
                        // 获取每台设备搜索区间内第一次数据上传时间
                        foreach (DeviceEfficiencyDto device in deviceEfficiency)
                        {
                            // 第一次上传数据时间比搜索起始时间早
                            if (DateTime.Parse(condition.StartTime).Subtract(device.DateStr).TotalDays > 0)
                            {
                                // 搜索结束时间-开始时间
                                device.TotalDays = (int)DateTime.Parse(condition.EndTime)
                                    .Subtract(DateTime.Parse(condition.StartTime)).TotalDays + 1;
                            }
                            else
                            {
                                // 搜索结束数据-第一次数据上传时间
                                device.TotalDays =
                                    (int)DateTime.Parse(condition.EndTime).Subtract(device.DateStr).TotalDays + 1;
                            }
                            totalDays += device.TotalDays;
                        }
                    }

                    // 总设备数
                    if (ds.Tables[0].Rows.Count == 0
                        || ds.Tables[1].Rows.Count == 0
                        )
                    {
                        summayWorkEfficiency = new SummayWorkEfficiency()
                        {
                            TotalDevices = ds.Tables[0].Rows.Count > 0 ? int.Parse(ds.Tables[0].Rows[0][0].ToString()) : 0,   // 设备总数
                            TotalPeople = ds.Tables[1].Rows.Count > 0 ? int.Parse(ds.Tables[1].Rows[0][3].ToString()) : 0,
                            TotalWorkingDays = ds.Tables[1].Rows.Count > 0 ? int.Parse(ds.Tables[1].Rows[0][0].ToString()) : 0,
                            TotalArea = ds.Tables[1].Rows.Count > 0 ? decimal.Parse(ds.Tables[1].Rows[0][1].ToString()) : 0,
                            TotalDays = totalDays,
                            WorkEfficiency = "0",
                            TimeEfficiency = "0",
                            Efficiency = "0",
                        };
                        return summayWorkEfficiency;
                    }

                    summayWorkEfficiency = new SummayWorkEfficiency()
                    {
                        TotalDevices = int.Parse(ds.Tables[0].Rows[0][0].ToString()),
                        TotalPeople = int.Parse(ds.Tables[1].Rows[0][3].ToString()),
                        TotalWorkingDays = int.Parse(ds.Tables[1].Rows[0][0].ToString()),
                        TotalArea = decimal.Parse(ds.Tables[1].Rows[0][1].ToString()),
                        TotalDays = totalDays,
                    };
                }
            }
            summayWorkEfficiency.CompanyName = tokenModel.Company;
            summayWorkEfficiency.WorkEfficiency = summayWorkEfficiency.TotalArea > 0 && summayWorkEfficiency.TotalWorkingDays > 0 ? (summayWorkEfficiency.TotalArea / summayWorkEfficiency.TotalWorkingDays).RoundToString() : "0";   // 工效==万平方米转平方米
            summayWorkEfficiency.TimeEfficiency = ((decimal)summayWorkEfficiency.TotalWorkingDays / summayWorkEfficiency.TotalDays * 100).RoundToString();  // 时效
            summayWorkEfficiency.Efficiency =
                    (decimal.Parse(summayWorkEfficiency.WorkEfficiency) *
                     (decimal.Parse((summayWorkEfficiency.TotalWorkingDays * 1.0 / summayWorkEfficiency.TotalDays).ToString()))).RoundToString();   // 工效×时效==效率
            summayWorkEfficiency.TotalArea = summayWorkEfficiency.TotalArea.RoundToString().ToDecimal(); // 万平方(保留两位小数)

            #endregion 总工效信息

            return summayWorkEfficiency;
        }

        /// <summary>
        /// 作者：黄钧航
        /// 下载Excel总工效数据
        /// </summary>
        /// <returns></returns>
        public async Task<string> DownloadEfficiencySummaryData(TimeConditon condition)
        {
            if (condition is null)
            {
                throw new ArgumentNullException(nameof(condition));
            }

            #region 准备模板路径和excel导出路径

            var templateName = "汇总工效统计表模板-v3.xlsx";

            var templatePath = Path.Combine(uploadFileOptions.ParentDir, "SystemFiles/ExcelTemplates", templateName);

            #endregion 准备模板路径和excel导出路径

            #region 总工效信息

            var summayWorkEfficiency = await GetEfficiencySummaryData(condition);
            if (summayWorkEfficiency == null)
            {
                throw new OperationException(Localizer["YB0184"].Value);
            }

            string companyName = summayWorkEfficiency.CompanyName;

            #endregion 总工效信息

            #region 工效详情明细查询Sql

            string sql = string.Empty;

            // 搜索区间内有数据记录的第一天，1.等于搜索起始时间；2.大于搜索起始时间
            DateTime startTime = DateTime.Parse(condition.StartTime);
            DateTime endTime = DateTime.Parse(condition.EndTime);

            List<NpgsqlParameter> parameters = new List<NpgsqlParameter>
            {
                new NpgsqlParameter("startDate",startTime),

                new NpgsqlParameter("endDate",endTime),

                new NpgsqlParameter("CompanyId",tokenModel.CompanyGuid)
            };

            List<SummayWorkEfficiency> summayWorkEfficiencys = new List<SummayWorkEfficiency>
            {
                summayWorkEfficiency
            };
            string dateStr = DateTime.Now.ToString();
            var userConditionSql = string.Empty;
            var projectLimitation = string.Empty;

            if (!tokenModel.IsCompanyAdmin)
            {
                //非公司管理员需要添加用户限制条件
                userConditionSql = await GetRelationUserSql("T2", "YunUserGuid");

                projectLimitation = @"AND EXISTS (SELECT 1 FROM ""MsUserToProject"" AS up WHERE up.""YunProjectGuid"" = pe.""ProjectGuid"" AND up.""YunUserGuid"" = @userId)";
                parameters.Add(new NpgsqlParameter()
                {
                    ParameterName = "userId",
                    Value = tokenModel.Id,
                });
            }

            sql = string.Format(
                VisionCloudGlobalContext.GetSection("sqls:sql:query_download_efficiency_summary_data"),
                projectLimitation, userConditionSql);
            var queryReult = new List<PersonnelEfficiencyDetailInfo>();

            #endregion 工效详情明细查询Sql

            #region 机器工效和机器详情信息

            List<DeviceEfficiencyDetail> deviceEfficiencyDetails = new List<DeviceEfficiencyDetail>();
            var deviceFirstDate = new Dictionary<string, DateTime>();
            var noWokrUsers = new Dictionary<string, string>();//无工作的用户账号和姓名
            using (DataSet ds = PgSqlHelper.ExecuteDataSet(sql, parameters.ToArray()))
            {
                if (ds?.Tables?.Count > 0 && ds?.Tables[0]?.Rows?.Count > 0)
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        var deviceEfficiencyDetail = new PersonnelEfficiencyDetailInfo
                        {
                            DeviceNumber = row["DeviceNumber"].ToString(),
                            BuildingNum = row["BuildingNum"].ToString(),
                            ProjectManager = row["ProjectManager"].ToString(),
                            ProjectName = row["ProjectName"].ToString(),
                            ProjectNo = row["ProjectNo"].ToString(),
                            RegionalCompany = row["RegionalCompany"].ToString(),
                            TaskProperty = row["TaskProperty"].ToString(),
                            ProjectStandardName = row["ProjectStandardName"].ToString(),
                            TaskTypeName = row["TaskTypeName"].ToString(),
                            YunUserName = row["YunUserName"].ToString(),
                            FloorNum = row["FloorNum"].ToInt32(),
                            TotalArea = row["TotalAreas"].ToDecimal(),
                            CompletedDate = DateTime.Parse(row["CompletedDate"].ToString()),
                            Account = row["PhoneNumber"].ToString(),
                            City = row["City"].ToString(),
                            Province = row["Province"].ToString(),
                            ProjectAddress = row["Address"].ToString(),
                            Town = row["Town"].ToString(),
                            OnlyCollectMode = row["OnlyCollectMode"].ToString(),
                        };
                        deviceEfficiencyDetails.Add(deviceEfficiencyDetail);
                        queryReult.Add(deviceEfficiencyDetail);
                    }

                    if (ds?.Tables[1]?.Rows?.Count > 0)
                    {
                        foreach (DataRow row in ds.Tables[1].Rows)
                        {
                            deviceFirstDate.Add(row["DeviceNumber"].ToString(), DateTime.Parse(row["FirstDate"].ToString()));
                        }
                    }

                    if (ds?.Tables[2]?.Rows?.Count > 0)
                    {
                        foreach (DataRow row in ds.Tables[2].Rows)
                        {
                            var account = row["PhoneNumber"]?.ToString();
                            var name = row["Name"].ToString();
                            if (!queryReult.Any(x => x.YunUserName == name && x.Account == account))
                            {
                                noWokrUsers.Add(account, name);
                            }
                        }
                    }
                }
            }
            if (deviceEfficiencyDetails.Count > 0 && deviceFirstDate.Count > 0)
            {
                //添加空行
                deviceEfficiencyDetails.AddNullRowsExt(condition, deviceFirstDate);
                //快速排序
                deviceEfficiencyDetails.Sort();
            }

            #region 各机器工效

            List<DeviceEfficiency> deviceEfficiency = new List<DeviceEfficiency>();
            foreach (var deviceWorkInfo in queryReult.GroupBy(x => x.DeviceNumber))
            {
                var totalDays = deviceWorkInfo.Select(x => x.CompletedDate).Distinct().Count();//总工作天数
                var TotalArea = deviceWorkInfo.Sum(x => x.TotalArea);
                var firstWorkDay = deviceWorkInfo.Min(x => x.CompletedDate);//搜索区间内工作的第一天
                int idleDay = firstWorkDay.CompareTo(startTime) > 0 ? endTime.Subtract(firstWorkDay).TotalDays.ToInt32() - totalDays ://停留天数
                                                                endTime.Subtract(startTime).TotalDays.ToInt32() - totalDays;
                deviceEfficiency.Add(new DeviceEfficiency
                {
                    DeviceNumber = deviceWorkInfo.Key,
                    TotalArea = TotalArea,
                    TotalDays = totalDays,
                    TotalFloors = deviceWorkInfo.Select(x => new { x.ProjectNo, x.BuildingNum, x.FloorNum, x.ProjectStandardName, x.TaskTypeName, x.TaskProperty }).Distinct().Count(),
                    StructureTestAreas = deviceWorkInfo.Where(x => x.ProjectStandardName == "结构工程" && x.TaskProperty == "初测").Sum(x => x.TotalArea),
                    StructureRetestAreas = deviceWorkInfo.Where(x => x.ProjectStandardName == "结构工程" && x.TaskProperty == "复测").Sum(x => x.TotalArea),
                    StructureOtherAreas = deviceWorkInfo.Where(x => x.ProjectStandardName == "结构工程" && x.TaskProperty != "初测" && x.TaskProperty != "复测").Sum(x => x.TotalArea),
                    MasonryTestAreas = deviceWorkInfo.Where(x => x.ProjectStandardName == "砌筑工程" && x.TaskProperty == "初测").Sum(x => x.TotalArea),
                    MasonryRetestAreas = deviceWorkInfo.Where(x => x.ProjectStandardName == "砌筑工程" && x.TaskProperty == "复测").Sum(x => x.TotalArea),
                    MasonryOtherAreas = deviceWorkInfo.Where(x => x.ProjectStandardName == "砌筑工程" && x.TaskProperty != "初测" && x.TaskProperty != "复测").Sum(x => x.TotalArea),
                    PlasteTestAreas = deviceWorkInfo.Where(x => x.ProjectStandardName == "抹灰工程" && x.TaskProperty == "初测").Sum(x => x.TotalArea),
                    PlasteRetestAreas = deviceWorkInfo.Where(x => x.ProjectStandardName == "抹灰工程" && x.TaskProperty == "复测").Sum(x => x.TotalArea),
                    PlasteOtherAreas = deviceWorkInfo.Where(x => x.ProjectStandardName == "抹灰工程" && x.TaskProperty != "初测" && x.TaskProperty != "复测").Sum(x => x.TotalArea),
                    DecorateTestAreas = deviceWorkInfo.Where(x => x.ProjectStandardName == "装饰工程" && x.TaskProperty == "初测").Sum(x => x.TotalArea),
                    DecorateRetestAreas = deviceWorkInfo.Where(x => x.ProjectStandardName == "装饰工程" && x.TaskProperty == "复测").Sum(x => x.TotalArea),
                    DecorateOtherAreas = deviceWorkInfo.Where(x => x.ProjectStandardName == "装饰工程" && x.TaskProperty != "初测" && x.TaskProperty != "复测").Sum(x => x.TotalArea),
                    WorkEfficiency = TotalArea / totalDays,
                    YunUserName = string.Join(',', deviceWorkInfo.Select(x => x.YunUserName).Distinct()),
                    IdleDay = idleDay
                });
            }
            deviceEfficiency = deviceEfficiency.OrderByDescending(x => x.TotalArea).ToList();

            #endregion 各机器工效

            #endregion 机器工效和机器详情信息

            #region 公司各项目工效

            List<ProjectWorkEfficiency> projectWorkEfficiency = new List<ProjectWorkEfficiency>();
            foreach (var pEf in queryReult.GroupBy(x => new { x.ProjectNo, x.ProjectName, x.Province, x.RegionalCompany, x.ProjectAddress, x.City, x.Town }))
            {
                var projectInfo = pEf.Key;
                projectWorkEfficiency.Add(new ProjectWorkEfficiency
                {
                    ProjectNo = projectInfo.ProjectNo,
                    ProjectName = projectInfo.ProjectName,
                    Province = projectInfo.Province,
                    ProjectAddress = projectInfo.ProjectAddress,
                    City = projectInfo.City,
                    Town = projectInfo.Town,
                    RegionalCompany = projectInfo.RegionalCompany,
                    TotalArea = pEf.Sum(x => x.TotalArea),
                    TotalDays = pEf.Select(x => x.CompletedDate).Distinct().Count(),
                    TotalFloors = pEf.Select(x => new { x.BuildingNum, x.FloorNum, x.ProjectStandardName, x.TaskTypeName, x.TaskProperty }).Distinct().Count(),
                });
            }
            projectWorkEfficiency = projectWorkEfficiency.OrderByDescending(x => x.TotalArea).ToList();

            #endregion 公司各项目工效

            #region 公司各测量员工效

            List<UserWorkEfficiency> userWorkEfficiency = new List<UserWorkEfficiency>();
            var seqNo = 1;
            foreach (var uwf in queryReult.GroupBy(x => new { x.YunUserName, x.Account }))
            {
                var totalArea = uwf.Sum(x => x.TotalArea);
                var totalDays = uwf.Select(x => x.CompletedDate).Distinct().Count();
                userWorkEfficiency.Add(new UserWorkEfficiency
                {
                    TotalArea = totalArea,
                    Account = uwf.Key.Account,
                    UserName = uwf.Key.YunUserName,
                    TotalWorkingDays = totalDays,
                    WorkEfficiency = totalArea / totalDays,
                    TotalFloors = uwf.Select(x => new { x.ProjectNo, x.BuildingNum, x.FloorNum, x.ProjectStandardName, x.TaskTypeName, x.TaskProperty }).Distinct().Count(),
                });
            }
            userWorkEfficiency = userWorkEfficiency.OrderByDescending(x => x.TotalArea).ToList();
            userWorkEfficiency.ForEach(x => x.SeqNo = seqNo++.ToString());
            //添加未工作测量员的记录
            foreach (var item in noWokrUsers)
            {
                userWorkEfficiency.Add(new UserWorkEfficiency
                {
                    SeqNo = seqNo++.ToString(),
                    Account = item.Key,
                    UserName = item.Value
                });
            }

            #endregion 公司各测量员工效

            #region 生成EXCEL

            var excelPath = Path.Combine(uploadFileOptions.ParentDir, uploadFileOptions.TemporaryFiles);
            if (!Directory.Exists(excelPath)) Directory.CreateDirectory(excelPath);
            excelPath = Path.Combine(excelPath, companyName + $"-{Localizer["workEfficiencyReport"].Value}_{StatisService.GetRandomNumber()}.xlsx");

            Configurator.Put(".xlsx", new WorkbookLoader());
            try
            {
                var data = new
                {
                    CompanyName = companyName,
                    StartTime = condition.StartTime,
                    EndTime = condition.EndTime,
                    A = summayWorkEfficiencys,
                    B = deviceEfficiency,
                    C = deviceEfficiencyDetails,
                    D = projectWorkEfficiency,
                    E = userWorkEfficiency
                };
                IExportFileByTemplate exporter = new ExcelExporter();
                await exporter.ExportByTemplate(excelPath, data, templatePath);
            }
            catch (Exception e)
            {
                Logger.Error("生成进度Excel异常", e);
            }

            #endregion 生成EXCEL

            return excelPath.Substring(uploadFileOptions.ParentDir.Length).Replace("\\", "/");
        }

        /// <summary>
        /// 获取某段时间内有上传数据的设备的累计测量面积和累计作业时长
        /// </summary>
        /// <param name="start_time">开始时间</param>
        /// <param name="end_time">结束时间</param>
        /// <returns></returns>
        public async Task<List<DevicePushFMSDto>> GetLastDeviceStitisticsData(DateTime start_time, DateTime end_time)
        {
            if (start_time > end_time)
                throw new OperationException("开始时间不能大于结束时间");

            //查询最近有上传数据的设备
            var list = await CSMSTaskRepository.GetLastUploadTaskDeviceAsync(start_time, end_time);
            if (list == null || list.Count == 0)
                return new List<DevicePushFMSDto>();

            return await GetDeviceStitisticsStat(list, start_time, end_time);
        }

        /// <summary>
        /// 获取设备的总测量统计和某个时间段内的每日统计
        /// </summary>
        /// <param name="list"></param>
        /// <param name="start_time"></param>
        /// <param name="end_time"></param>
        /// <returns></returns>
        public async Task<List<DevicePushFMSDto>> GetDeviceStitisticsStat(List<DeviceProjectPushDto> list, DateTime start_time, DateTime end_time)
        {
            List<DevicePushFMSDto> pushList = new List<DevicePushFMSDto>();

            //获取设备截止时间内的总统计数据
            var deviceIds = list.Select(p => p.DeviceGuid).ToList();
            var statList = await CSMSTaskRepository.GetDeviceStitisticsAsync(deviceIds, end_time);
            if (statList == null || statList.Count == 0)
                return pushList;

            foreach (var item in statList)
            {
                var device = list.FirstOrDefault(p => p.DeviceGuid == item.Id);
                pushList.Add(new DevicePushFMSDto
                {
                    Active = device.IsActive,
                    DeviceCode = device.DeviceNumber,
                    TotalMeasureArea = item.TotalAreas,
                    TotalWorkingDuration = item.WorkDays,
                    ProjectName = device.ProjectName,
                    SoftwareVersion = device.DeviceSoftwareVersion,
                    UploadTime = device.ReportUploadDateTime.ToDateTimeStandardString()
                });
            }

            //获取设备某段时间内的每日统计数据
            var dayStatList = await CSMSTaskRepository.GetDeviceDayMeasureStatAsync(start_time, end_time);
            if (dayStatList != null && dayStatList.Count > 0)
            {
                foreach (var push in pushList)
                {
                    var tempList = dayStatList.Where(p => p.DeviceNumber == push.DeviceCode).ToList();
                    foreach (var temp in tempList)
                    {
                        push.MeasureDetailList.Add(new DeviceDateMeasureDetailDto
                        {
                            Date = temp.CompletedDate.ToDateStandardString(),
                            ProjectName = temp.ProjectName,
                            MeasureArea = temp.TotalAreas
                        });
                    }
                    push.MeasureDetailList = push.MeasureDetailList.OrderBy(p => p.Date).ToList();
                }
            }

            return pushList;
        }
    }
}